package com.dao;
import com.domain.AdminBean;
import com.domain.Grade;
import com.domain.StudentBean;
import com.tools.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    public static List<StudentBean> selectAll() {
        List<StudentBean> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from student";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int sId = rs.getInt("sId");
                String sName = rs.getString("sName");
                int sAge = rs.getInt("sAge");
                String sSex = rs.getString("sSex");
                String sAddress = rs.getString("sAddress");
                String sDate = rs.getString("sDate");
                int gradeId = rs.getInt("gradeId");

                //将结果集的每行数据存进对象list中
                StudentBean z = new StudentBean();
                z.setSId(sId);
                z.setSName(sName);
                z.setSAge(sAge);
                z.setSSex(sSex);
                z.setSAddress(sAddress);
                z.setSDate(sDate);
                z.setGradeId(gradeId);

                //将对象封装到List中
                list.add(z);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            com.tools.DBUtil.close(conn, ps, rs);
        }
        return null;
    }

    /**
     * 查询学生信息
     * @param gra
     * @return
     */
    public static List<Grade> selectId(int gra) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Grade> list = new ArrayList<>();
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from grade where gradeId = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,gra);
            rs = ps.executeQuery();
            while (rs.next()) {
                int gradeId = rs.getInt("gradeId");
                int Chinese = rs.getInt("Chinese");
                int Math = rs.getInt("Math");
                int English = rs.getInt("English");
                int Java = rs.getInt("Java");
                int Oracle = rs.getInt("Oracle");

                //将结果集的每行数据存进对象list中
                Grade g = new Grade(gradeId, Chinese, Math, English, Java, Oracle);
                list.add(g);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, rs);
        }
        return null;
    }

    /**
     * 查询管理员信息
     * @return
     */
    public static List<AdminBean> selectAd() {
        List<AdminBean> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from admin";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int loginId = rs.getInt("loginId");
                String loginPwd = rs.getString("loginPwd");

                //将结果集的每行数据存进对象list中
                AdminBean a = new AdminBean();
                a.setLoginId(loginId);
                a.setLoginPwd(loginPwd);

                //将对象封装到List中
                list.add(a);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            com.tools.DBUtil.close(conn, ps, rs);
        }
        return null;
    }

    /**
     * 添加学生信息
     * @param studentBean
     */
    public static void addStudent(StudentBean studentBean) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            conn=DBUtil.getConnection();
            String sql="insert into student (sId,sName,sAge,sSex,sAddress,sDate,gradeId) values(?,?,?,?,?,?,?)";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,studentBean.getSId());
            ps.setString(2,studentBean.getSName());
            ps.setInt(3,studentBean.getSAge());
            ps.setString(4,studentBean.getSSex());
            ps.setString(5,studentBean.getSAddress());
            ps.setString(6,studentBean.getSDate());
            ps.setInt(7,studentBean.getGradeId());
            int count=ps.executeUpdate();
            System.out.println("受影响的条数："+count);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
    }

    /**
     * 添加学生成绩
     * @param grade
     */
    public static void addGrade(Grade grade) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            conn=DBUtil.getConnection();
            String sql="insert into grade(gradeId,Chinese,Math,English,Java,Oracle) values (?,?,?,?,?,?)";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,grade.getGradeId());
            ps.setInt(2,grade.getChinese());
            ps.setInt(3,grade.getMath());
            ps.setInt(4,grade.getEnglish());
            ps.setInt(5,grade.getJava());
            ps.setInt(6,grade.getOracle());
            int count=ps.executeUpdate();
            System.out.println("受影响的条数："+count);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DBUtil.close(conn,ps,rs);
        }
    }

    /**
     * 删除学生信息
     * @param id
     */
    public static void deleteStudent(int id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from student where sId=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            itSystemCase7.tools.DBUtil.close(conn, ps, null);
        }
    }

    public void updateStudentName(int id, String name) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "update student set sName=? where sId=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setInt(2,id);
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }
    }

    public void updateStudentAge(int id, int age) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "update student set sAge=? where sId=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,age);
            ps.setInt(2,id);
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    public void updateStudentSex(int id, String sex) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "update student set sSex=? where sId=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,sex);
            ps.setInt(2,id);
            ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    public void updateStudentAddress(int id, String address) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "update student set sAddress=? where sId=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,address);
            ps.setInt(2,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    public void updateStudentDate(int id, String birth) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "update student set sDate=? where sId=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,birth);
            ps.setInt(2,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }
}
